<?php

namespace app\command;

use app\model\OrderModel;
use PhpOffice\PhpSpreadsheet\IOFactory;
use think\console\Command;
use think\console\Input;
use think\console\input\Argument;
use think\console\input\Option;
use think\console\Output;
use think\facade\Db;
use think\facade\Log;

class ParseExcel extends Command
{
    const AKG    = 'akg';
    const HUFU   = 'hufu';
    const KANGAI = 'kangai';
    public $filename = '20250117.xlsx';

    // baidu   tencent
    public $addr_type = 'tencent';

    public $flag = false;

    protected function configure()
    {
        // 指令配置
        $this->setName('parse_excel')
            ->setDescription('the parse_excel command');
    }

    protected function execute(Input $input, Output $output)
    {
        try {
            $oneOrder = OrderModel::where("1=1")->find();
            if ($oneOrder && $this->flag) {
                $order_sn = $oneOrder['order_sn'];
                $suffix   = substr($order_sn, 4, 4);

                $oldName = 'order';
                $newName = 'order_' . $suffix;
                Db::execute("RENAME TABLE `{$oldName}` TO `{$newName}`");

                $create_sql = "CREATE TABLE `order` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `order_sn` varchar(32) NOT NULL DEFAULT '' COMMENT '订单编号',
  `amount` decimal(11,2) NOT NULL DEFAULT '0.00' COMMENT '订单金额',
  `order_create_time` datetime DEFAULT NULL COMMENT '订单创建时间',
  `customer` varchar(128) NOT NULL DEFAULT '' COMMENT '联系人',
  `mobile` varchar(32) NOT NULL DEFAULT '' COMMENT '手机号',
  `address` varchar(255) NOT NULL DEFAULT '' COMMENT '地址',
  `is_combine` tinyint(11) NOT NULL DEFAULT '0' COMMENT '1：组合 0：非组合',
  `pink_cnt` int(11) NOT NULL DEFAULT '0' COMMENT '粉的数量',
  `blue_cnt` int(11) NOT NULL DEFAULT '0' COMMENT '蓝的数量',
  `is_lady` tinyint(11) NOT NULL DEFAULT '0' COMMENT '1：男 0：女',
  `combine_cnt` tinyint(11) NOT NULL DEFAULT '0',
  `type` tinyint(11) NOT NULL DEFAULT '1',
  `idcard` varchar(255) NOT NULL DEFAULT '',
  `old_address` varchar(255) NOT NULL DEFAULT '' COMMENT '原始地址',
  PRIMARY KEY (`id`),
  KEY `order_sn` (`order_sn`)
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4";
                Db::execute($create_sql);
            }
            Db::startTrans();
            $this->insertExcel($output);
            // 指令输出
            $output->writeln('finish parse_excel');

            Db::commit();
        } catch (\Exception $e) {
            Db::rollback();
            $output->writeln($e->getMessage());
        }
    }

    public function insertExcel(Output $output)
    {
        $objReader     = IOFactory::createReader('Xlsx');
        $filename      = $this->filename;
        $objPHPExcel   = $objReader->load(root_path() . '/public/' . $filename);//传入的文件
        $sheet         = $objPHPExcel->getSheet(0);   //excel中的第一张sheet
        $highestRow    = $sheet->getHighestRow();       // 取得总行数
        $highestColumn = $sheet->getHighestColumn();   // 取得总列数
        \PhpOffice\PhpSpreadsheet\Cell\Coordinate::columnIndexFromString($highestColumn);
        $lines        = $highestRow - 1;
        $access_token = getAccessToken();
        if ($lines <= 0) {
            return json(['code' => 0, 'message' => '没有数据！']);
        }
        $data = [];
        for ($j = 2; $j <= $highestRow; $j++) {
            $goods_info_one   = [];
            $is_combine       = false;
            $next_order_sn    = '';
            $data['order_sn'] = $objPHPExcel->getActiveSheet()->getCell("A" . $j)->getValue();
            $next_order_sn    = $objPHPExcel->getActiveSheet()->getCell("A" . ($j + 1))->getValue();
            $next_goods_info  = $objPHPExcel->getActiveSheet()->getCell("Q" . ($j + 1))->getValue();
            $m                = 0;

            if (empty($next_order_sn)) {  // 组合
                // 找到不为空的
                $m    = 1;
                $flag = true;
                do {
                    $order_sn = $objPHPExcel->getActiveSheet()->getCell("A" . ($j + $m + 1))->getValue();
                    if (!empty($order_sn) || $j >= $highestRow) {
                        $flag = false;
                        break;
                    }
                    $m++;
                    if ($m >= 5) {
                        $flag = false;
                        break;
                    }
                } while ($flag);

                for ($n = 1; $n <= $m; $n++) {
                    $index      = $j + $n;
                    $goods_info = $objPHPExcel->getActiveSheet()->getCell("Q" . $index)->getValue();
                    if ($goods_info) {
                        $goods_info     = trim($goods_info);
                        $one            = $this->parseSexCnt($goods_info);
                        $goods_info_one = array_merge($goods_info_one, $one);
                    }
                }
                if (!empty($next_goods_info)) $is_combine = true;
            }

            $data['order_sn']          = trim($data['order_sn']);
            $data['amount']            = trim($objPHPExcel->getActiveSheet()->getCell("C" . $j)->getValue());
            $data['order_create_time'] = trim($objPHPExcel->getActiveSheet()->getCell("G" . $j)->getValue());
            $data['customer']          = trim($objPHPExcel->getActiveSheet()->getCell("I" . $j)->getValue());
            $data['mobile']            = trim($objPHPExcel->getActiveSheet()->getCell("J" . $j)->getValue());
            $data['address']           = trim($objPHPExcel->getActiveSheet()->getCell("K" . $j)->getValue());
            $data['old_address']       = $data['address'];
//            $data['address']           = preg_replace('/[^a-zA-Z0-9\p{Han}\/\-\.]/u', '', $data['address']);
            $is_exists = OrderModel::where('order_sn', trim($data['order_sn']))->find();
            if (!$is_exists) {
                if (strpos($data['address'], "中国") !== false) {
                    $data['type'] = 1;
                    if ($this->addr_type == 'baidu') {
                        $data['address'] = baidu_address_parse($data['address'], $access_token);
                    } else {
                        $data['address'] = tecent_address_parse($data['address']);
                    }
                    print_r($data['address']);
                } else {    // 不包含
                    $data['type'] = 2;
                }
            }

            $goods_info = trim($objPHPExcel->getActiveSheet()->getCell("Q" . $j)->getValue());
            $result     = $this->parseSexCnt($goods_info);
            $multi_arr  = [];
            if ($is_combine) {
                $data['is_combine'] = 1;
                $result             = array_merge($result, $goods_info_one);
                $goods_type_arr     = array_column($result, "goods_type");
                if (!in_array("akg", $goods_type_arr)) {    // 拆成多单
                    foreach ($result as $key => $tmp_item) {
                        $tmp_data               = $data;
                        $tmp_data['is_combine'] = 0;
                        $tmp_data['blue_cnt']   = $tmp_item['cnt'];
                        $tmp_data['goods_type'] = $tmp_item['goods_type'];
                        $multi_arr[]            = $tmp_data;
                    }
                } else {
                    $data['goods_type'] = 'akg';
                    OrderModel::create($data);
                }
            } else {
                $goods_info         = $objPHPExcel->getActiveSheet()->getCell("Q" . $j)->getValue();
                $goods_type         = $this->getGoodsType($goods_info);
                $data['goods_type'] = $goods_type;
                $data['is_combine'] = 0;
            }
            $res                 = $this->parseSexCntArr($result);
            $data['pink_cnt']    = $res['pink_cnt'];
            $data['blue_cnt']    = $res['blue_cnt'];
            $data['is_lady']     = $res['is_lady'] == false ? 0 : 1;
            $data['combine_cnt'] = $this->combineCnt($data['pink_cnt'], $data['blue_cnt']);

            //
            $idcard         = trim($objPHPExcel->getActiveSheet()->getCell("M" . $j)->getValue());
            $data['idcard'] = $idcard;

            if (!$is_exists) {
                $output->writeln("$j == 订单号：" . $data['order_sn']);
                if (empty($multi_arr)) {
                    OrderModel::create($data);
                } else {
                    (new OrderModel())->saveAll($multi_arr);
                }
            } else {
                $output->writeln("$j == 订单号已存在：" . $data['order_sn']);
//                $is_exists->save($data);
            }
            if ($is_combine) $j += $m;

//            $output->writeln($j);
        }
    }

    // 判断是组合几
    public function combineCnt($pink_cnt, $blue_cnt)
    {
        if ($blue_cnt == 1 && $pink_cnt == 1) return 1;
        if ($blue_cnt == 2 && $pink_cnt == 1) return 2;
        if ($blue_cnt == 4 && $pink_cnt == 1) return 3;
        if ($blue_cnt == 1 && $pink_cnt == 4) return 4;
        if ($blue_cnt == 1 && $pink_cnt == 2) return 5;
        if ($blue_cnt == 2 && $pink_cnt == 2) return 6;
//        if ($blue_cnt == 0 && $pink_cnt == 5) return 7;
        if ($blue_cnt == 3 && $pink_cnt == 1) return 8;
        if ($blue_cnt == 1 && $pink_cnt == 3) return 9;
        if ($blue_cnt == 2 && $pink_cnt == 3) return 10;
        if ($blue_cnt == 3 && $pink_cnt == 2) return 11;
        if ($blue_cnt == 5 && $pink_cnt == 5) return 12;
//        if ($blue_cnt == 0 && $pink_cnt == 4) return 13;
//        if ($blue_cnt == 4 && $pink_cnt == 0) return 14;
        if ($blue_cnt == 3 && $pink_cnt == 3) return 15;
        if ($blue_cnt == 2 && $pink_cnt == 4) return 16;
        if ($blue_cnt == 4 && $pink_cnt == 4) return 17;
//        if ($blue_cnt == 5 && $pink_cnt == 0) return 18;
        if ($blue_cnt == 5 && $pink_cnt == 1) return 19;
//        if ($blue_cnt == 6 && $pink_cnt == 0) return 20;
        return 0;
    }

    /**
     * 处理性别和数量的二维数组
     * @param $arr
     * @return void
     */
    public function parseSexCntArr($arr)
    {
        $result = [
            'pink_cnt' => 0,
            'blue_cnt' => 0,
            'is_lady'  => false
        ];
        if (count($arr) == 1) { // 非组合
            $result['is_lady'] = $arr[0]['is_lady'];
        }
        foreach ($arr as $k => $v) {
            if ($v['is_lady'] == false) {
                $result['blue_cnt'] += $v['cnt'];
            } else {
                $result['pink_cnt'] += $v['cnt'];
            }
        }
        return $result;
    }

    /**
     * 解析数量和性别
     * @param $str
     * @return array
     */
    public function parseSexCnt($str)
    {
        // 判断是男士还是女士
        $isLady = stripos($str, '女士') !== false;
        $gender = $isLady ? '女士' : '男士';

        // 判断商品类型
        $issvip = stripos($str, 'SVIP') !== false;
        $isvip  = stripos($str, 'VIP') !== false;
        $ismb   = stripos($str, 'MB') !== false;

        $istwo   = stripos($str, '两瓶') !== false;
        $isfour  = stripos($str, '四瓶') !== false;
        $isthree = stripos($str, '三瓶') !== false;
        $isfive  = stripos($str, '五瓶') !== false;

        // 提取数量
        preg_match('/商品数量：(\d+)/', $str, $matchesForQuantity);
        $quantity = isset($matchesForQuantity[1]) ? $matchesForQuantity[1] : 0;
        if ($issvip) {
            $quantity = $quantity * 5;
        } else if ($isvip) {
            $quantity = $quantity * 3;
        } else if ($istwo) {
            $quantity = $quantity * 2;
        } else if ($isfour) {
            $quantity = $quantity * 4;
        } else if ($isthree) {
            $quantity = $quantity * 3;
        } else if ($isfive) {
            $quantity = $quantity * 5;
        }

        $goods_type = $this->getGoodsType($str);
        return [['is_lady' => $isLady, 'cnt' => $quantity, 'goods_type' => $goods_type]];
    }

    public function getGoodsType($goods_name)
    {
        if (stripos($goods_name, "GnAKG") !== false) {
            return 'akg';
        } elseif (stripos($goods_name, "GnCell") !== false) {
            return 'cell';
        } elseif (stripos($goods_name, "护肤套盒") !== false) {
            return 'hufu';
        } elseif (stripos($goods_name, "招财猫") !== false) {
            return 'zcm';
        }
        return 'akg';
    }

    /**
     * 解析发货地址
     * @param $address
     * @return string
     */
    public function parseAddress($address)
    {
        $address       = implode("", array_unique(explode("/", $address)));
        $apiUrl        = 'http://cloud.kuaidi100.com/api';
        $secret_key    = "hjHPKEHrb7PL5Tp41Q";
        $secret_secret = "0d8e4d1bdd81447ba4f15111c304df9c";
        $secret_code   = "0db4f037e2094f15a811261722e8a6e9";

        // 构造请求数据
        $data                = [
            'secret_key'  => $secret_key,
            'secret_code' => $secret_code,
        ];
        $data['secret_sign'] = strtoupper(md5($secret_key . $secret_secret));
        $data['content']     = $address;

        // 使用 cURL 发送请求
        $ch = curl_init();
        curl_setopt($ch, CURLOPT_URL, $apiUrl);
        curl_setopt($ch, CURLOPT_POST, true);
        curl_setopt($ch, CURLOPT_POSTFIELDS, http_build_query($data));
        curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
        // 关闭SSL证书验证
        curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, false);
        $response = curl_exec($ch);
        curl_close($ch);

        // 解析响应
        $responseData = json_decode($response, true);
        if ($responseData['code'] === 200) {
            $xzq      = $responseData['data'][0]['xzq'];
            $province = $xzq['province'];
            $city     = $xzq['city'];
            $district = $xzq['district'];

            $arr      = [$province, $city, $district, "省", "市", "县"];
            $sub_area = $xzq['subArea'];
            foreach ($arr as $value) {
                $sub_area = str_replace($value, '', $sub_area);
            }
            $new_address = "中国" . $province . $city . $district . $sub_area;
            return $new_address;
        } else {
            return $address;
        }
    }
}
